package com.topisv.tms.repository;



import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import com.topisv.tms.entity.EdiOrder;

public interface EdiOrderDao extends PagingAndSortingRepository<EdiOrder, Long>, JpaSpecificationExecutor<EdiOrder> {
	
	@Query("select max(waybillNo) from EdiOrder e where e.customerCode=? order by waybillNo desc")
	String findMaxWaybillNo(String customerCode);
	
	List<EdiOrder> findByGymemo(String gymemo);
	
	
	@Query("select count(*) from EdiOrder ediOrder where ediOrder.crtime between ?1 and ?2")
	long findByCrtimeAdtime(Date crtime,Date adtime);
	List<EdiOrder> findByCrtime(String crtime);
	@Query("select count(*) from EdiOrder ediOrder where ediOrder.crtime between ?1 and ?2 and ediOrder.orderType=?3")
	long findByCrtimeAdtime(Date crtime,Date adtime,String type);
	
	EdiOrder findByOrdeno(String ordeno);
	
	@Query("select t from EdiOrder t where t.ordeno in ?1")
	List<EdiOrder> findByOrdeno(List<String> ordeno);
	
	List<EdiOrder> findByCrtimeBetween(Date fromDate,Date endDate);
	
	List<EdiOrder> findByCustomerCodeAndFromno(String customerCode,String fromno);
	
	List<EdiOrder> findByCustomerCodeAndCustomerOrdeno(String customerCode,String customerOrdeno);
	
	@Query("select t from EdiOrder t where t.customerCode =?1 and t.customerOrdeno = ?2 and t.orderStatus ='ok'")
	List<EdiOrder> findByCustomerCodeAndCustomerOrdenoAndOrderStatus(String customerCode,String customerOrdeno);
		
	@Query("select ediOrder from EdiOrder ediOrder where ediOrder.provco =?1")
	List<EdiOrder> findByProvco(String provco);
	
	@Modifying
	@Query("update EdiOrder ediOrder set ediOrder.orderType=?1 where ediOrder.id=?2")
	void updateEdiOrder(String orderType,Long id);

	@Query("select t from EdiOrder t where t.ordeno=?1 or t.customerOrdeno=?1 or t.fromno=?1 ")
	List<EdiOrder> findByOrdernoOrCustomerorderno(String orderno);
	
	List<EdiOrder> findByCustomerOrdeno(String ordeno);
	
	@Modifying
	@Query("update EdiOrder ediOrder set ediOrder.dispatchFlag=1,ediOrder.warehouseCode=?2 where ediOrder.ordeno=?1")
	void updateEdiOrderDispatchFlag(String ordeno,String warehouseCode);
	@Modifying
	@Query("update EdiOrder ediOrder set ediOrder.dispatchFlag=0 where ediOrder.ordeno=?1")
	void updateEdiOrderDispatchFlagByOrdeno(String ordeno);
	
	@Query("select t from EdiOrder t where t.batchTaskCode=?1 and t.shopCode in ?2 ")
	List<EdiOrder> findBybatchTaskCode(String batchTaskCode,List<String> shopCode);
	
	@Query("select t.shopCode from EdiOrder t where t.batchTaskCode=?1 and t.shopCode in ?2 group by t.shopCode ")
	List<String> findBybatchTaskCodeGroup(String batchTaskCode,List<String> shopCode);
//t.customerCode =?1 and
	@Query("select t from EdiOrder t where t.customerCode =?1 and t.warehouseCode = ?2 and date_format(t.crtime,'%Y/%m/%d')>=?3 and date_format(t.crtime,'%Y/%m/%d')<=?4 and t.dispatchFlag='0' and t.orderStatus='ok' order by id")
	List<EdiOrder> findByEdiOrder(String customerCode,String warehouseCode,String crtime,String crtime1);
	
	@Query("select t from EdiOrder t where t.customerCode =?1 and t.areaGroupCode = ?2 and t.dispatchFlag =0")
	List<EdiOrder> findByCustomerCodeAndAreaGroupCode(String customerCode,String customerOrdeno);
	@Query("select t from EdiOrder t where t.customerCode =?1 and t.warehouseCode = ?2 and t.crtime between ?3 and ?4 and t.dispatchFlag =0 and t.orderStatus !='cancel' ")
	List<EdiOrder> findByCustomerCodeAndWarehouseCode(String customerCode,String warehouseCode, Date fromDate, Date toDate);
	
	@Modifying
	@Query("update EdiOrder ediOrder set ediOrder.regionalLevel=?2 where ediOrder.id=?1")
	void updateEdiOrderRegionalLevel(Long id,int regionalLevel);
	
	@Query("select t from EdiOrder t where t.customerCode =?1 and t.customerOrdeno = ?2")
	EdiOrder getCustomerOrdeno(String customerCode,String customerOrdeno);
	
	@Query("select shopCode from EdiOrder where ordeno=?1 ")
    String findShopCodeByOrderNo(String orderno);
	
	EdiOrder findByMailNo(String mailNo);
	
	@Modifying
	@Query("update EdiOrder t set t.prioritySendFlag=?1 where t.ordeno=?2")
	int updateEdiOrderPrioritySendFlag(String flag, String orderno);
	
	
	@Query(nativeQuery=true, value="SELECT CAST((@i \\:=@i + 1) AS CHAR) AS i,customer_code,ordeno,shopna,recena,addres,cacode,crtime,bumemo,id "
			+ "FROM edi_order a, (SELECT @i \\:= 0) b where  a.customer_code = :customerCodes  AND warehouse_code = :warehouseCode "
			+ "AND DATE_FORMAT(a.crtime, '%Y-%m-%d %H:%I') >= :fromDate "
			+ "AND DATE_FORMAT(a.crtime, '%Y-%m-%d %H:%I') <= :toDate "
			+ "AND a.bumemo is not null " 
			+ "AND (a.bumemo <> 'NULL' or a.bumemo <> 'null') "
            + "AND a.bumemo <> '' ")
	List<Object[]> exceptRemarkOrdernos(@Param("customerCodes") String customerCodes, @Param("warehouseCode") String warehouseCode, @Param("fromDate") String fromDate, @Param("toDate") String toDate);

	@Modifying
	@Query("update EdiOrder t set t.returnStatus=?1  where t.ordeno=?2")
	int updateReturnStatus(String status , String ordeno);
	
}
